package com.hwg.emp.dao.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.hwg.emp.common.dto.KeyQueryDto;
import com.hwg.emp.common.dto.KeysQueryDto;
import com.hwg.emp.common.util.DBUtil;
import com.hwg.emp.dao.AttendanceDAO;
import com.hwg.emp.dto.AttendanceDto;
import com.hwg.emp.dto.PageBean;
import com.hwg.emp.model.AttendanceMD;
import com.hwg.emp.model.EmployeeMD;

public class AttendanceDAOImpl implements AttendanceDAO{

	@Override
	public int insert(AttendanceMD t) throws Exception {
		return 0;
		
	}

	@Override
	public int delete(String uk, String ukValue) throws Exception {
		return 0;
		
	}

	@Override
	public int update(AttendanceMD t) throws Exception {
		return 0;
		
	}

	@Override
	public AttendanceMD select(String uk, String ukValue) throws Exception {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public boolean exist(String uk, String ukValue) throws Exception {
		// TODO Auto-generated method stub
		return false;
	}
	
	@Override
	public int selectEmpAttendance() throws Exception {
		// TODO Auto-generated method stub
		int account=0;
		String sql="SELECT COUNT( * ) scount FROM rcd_attendance,sys_user WHERE rcd_attendance.emp_id=sys_user.id";
		Connection con=DBUtil.getConnection();
		PreparedStatement pst=con.prepareStatement(sql);
		ResultSet rs=pst.executeQuery();
		if(rs.next()){
			account=rs.getInt("scount");
		}
		return account;
	}
	@Override
	public List<AttendanceDto> selectEmpAttendance(PageBean pageBean) throws Exception {
		// TODO Auto-generated method stub
		List<AttendanceDto> attendanceDtos=new ArrayList<AttendanceDto>();
		Connection con=DBUtil.getConnection();
		String sql="SELECT sys_user.`name`,rcd_attendance.starttime,rcd_attendance.endtime FROM rcd_attendance,sys_user WHERE rcd_attendance.emp_id=sys_user.id limit ?,?";
		PreparedStatement pst=con.prepareStatement(sql);
		pst.setInt(1,pageBean.getStart());
		pst.setInt(2, pageBean.getPrecord());
		ResultSet rs=pst.executeQuery();
		while(rs.next()){
			AttendanceDto attendanceDto=new AttendanceDto();
			attendanceDto.setName(rs.getString("name"));
			attendanceDto.setStartTime(rs.getDate("starttime"));
			attendanceDto.setEndTime(rs.getDate("endtime"));
			attendanceDtos.add(attendanceDto);
		}
		
		return attendanceDtos;
	}

	@Override
	public int selectAttendanceByKey(KeysQueryDto dto, List<AttendanceMD> pageData) throws Exception {
		// TODO Auto-generated method stub
		//查询总记录数
		int totalCount=0;
		Connection con =DBUtil.getConnection();
		StringBuffer whereSql=new StringBuffer();
		whereSql.append(" from rcd_attendance where 1=1 ");
		if (dto.getKeys().get("askoffdays")!=null) {
			whereSql.append(" or askoffdays =?");
		}
		if (dto.getKeys().get("startdate")!=null) {
			whereSql.append(" or startdate=?");
		}
		if (dto.getKeys().get("enddate")!=null) {
			whereSql.append(" or enddate=?");
		}
		if (dto.getKeys().get("overtimedays")!=null) {
			whereSql.append(" or overtimedays =?");
		}
		if (dto.getKeys().get("latetimes")!=null) {
			whereSql.append(" or latetimes =?");
		}
		if (dto.getKeys().get("absentdays")!=null) {
			whereSql.append(" or absentdays =?");
		}
		if (dto.getKeys().get("earlytimes")!=null) {
			whereSql.append(" or earlytimes =?");
		}
		if (dto.getKeys().get("checkdays")!=null) {
			whereSql.append(" or checkdays =?");
		}
		
		PreparedStatement pst=con.prepareStatement("select count(*)"+whereSql.toString());
		int i=0;
		if (dto.getKeys().get("askoffdays")!=null) {
			pst.setDouble(++i, (Integer)(dto.getKeys().get("askoffdays")));
		}
		if (dto.getKeys().get("startdate")!=null) {
			pst.setDate(++i, (Date)(dto.getKeys().get("startdate")));
		}
		if (dto.getKeys().get("enddate")!=null) {
			pst.setDate(++i, (Date)(dto.getKeys().get("enddate")));
		}
		if (dto.getKeys().get("overtimedays")!=null) {
			pst.setDouble(++i, (Integer)(dto.getKeys().get("overtimedays")));
		}
		if (dto.getKeys().get("latetimes")!=null) {
			pst.setDouble(++i, (Integer)(dto.getKeys().get("latetimes")));
		}
		if (dto.getKeys().get("absentdays")!=null) {
			pst.setDouble(++i, (Integer)(dto.getKeys().get("absentdays")));
		}
		if (dto.getKeys().get("earlytimes")!=null) {
			pst.setDouble(++i, (Integer)(dto.getKeys().get("earlytimes")));
		}
		if (dto.getKeys().get("checkdays")!=null) {
			pst.setDouble(++i, (Integer)(dto.getKeys().get("checkdays")));
		}
		ResultSet rs=pst.executeQuery();
		if (rs.next()) {
			totalCount=rs.getInt(1);
		}
		rs.close();
		pst.close();
		
		//分页查询
				StringBuilder selectSql=new StringBuilder();
				selectSql.append("select * ").append(whereSql);
				selectSql.append(" limit ").append((dto.getPageNo()-1)*dto.getPageSize())
				.append(",").append(dto.getPageSize());
				pst=con.prepareStatement(selectSql.toString());
				i=0;
				if (dto.getKeys().get("askoffdays")!=null) {
					pst.setDouble(++i, (Integer)(dto.getKeys().get("askoffdays")));
				}
				if (dto.getKeys().get("startdate")!=null) {
					pst.setDate(++i, (Date)(dto.getKeys().get("startdate")));
				}
				if (dto.getKeys().get("enddate")!=null) {
					pst.setDate(++i, (Date)(dto.getKeys().get("enddate")));
				}
				if (dto.getKeys().get("overtimedays")!=null) {
					pst.setDouble(++i, (Integer)(dto.getKeys().get("overtimedays")));
				}
				if (dto.getKeys().get("latetimes")!=null) {
					pst.setDouble(++i, (Integer)(dto.getKeys().get("latetimes")));
				}
				if (dto.getKeys().get("absentdays")!=null) {
					pst.setDouble(++i, (Integer)(dto.getKeys().get("absentdays")));
				}
				if (dto.getKeys().get("earlytimes")!=null) {
					pst.setDouble(++i, (Integer)(dto.getKeys().get("earlytimes")));
				}
				if (dto.getKeys().get("checkdays")!=null) {
					pst.setDouble(++i, (Integer)(dto.getKeys().get("checkdays")));
				}
				rs=pst.executeQuery();
				while (rs.next()) {
					AttendanceMD at=new AttendanceMD();
					EmployeeMD emp=new EmployeeMD();
					emp.setId(rs.getString("emp_id"));
					at.setId(rs.getString("id"));
					at.setStarttime(rs.getDate("starttime"));
					at.setEndtime(rs.getDate("endtime"));
					at.setAskoffdays(rs.getInt("askoffdays"));
					at.setOvertimedays(rs.getInt("overtimedays"));
					at.setLatetimes(rs.getInt("latetimes"));
					at.setAbsentdays(rs.getInt("absentdays"));
					at.setEarlytimes(rs.getInt("earlytimes"));
					at.setCheckdays(rs.getInt("checkdays"));
					pageData.add(at);
				}
		
		return totalCount;
	}

}
